﻿//-------------------------------------------------------------------------------------------------------------------------------------
// Copyright (C) 2005 greatelsoft.com All Rights Reserved
//-------------------------------------------------------------------------------------------------------------------------------------

using System;
using System.Data.Common;
using System.Data;
using System.Data.OleDb;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using GTS.MiniBox.Entity;
using Pub.Class;

namespace GTS.MiniBox.Helper {
    /// <summary>
    /// 操作类
    /// 
    /// 修改纪录
    ///     2012-08-31 版本：1.0 系统自动创建此类
    /// 
    /// </summary>
    public partial class BusinessSettlementHelper {
        /// <summary>
        /// 缓存多少秒 x 5
        /// </summary>
        public static int cacheSeconds = 1440;
        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="businessSettlement">实体类</param>
        /// <param name="delCache">添加成功后清理的CACHE key，支持正则</param>
        /// <param name="dbkey">存在数据库连接池中的连接key，为空时使用ConnString连接</param>
        /// <returns>添加是否成功</returns>
        public static bool Insert(BusinessSettlement businessSettlement, string dbkey = "", string[] delCache = null) {
            string strSql = new SQL().Insert(BusinessSettlement._)
                .Value(BusinessSettlement._StartTime, businessSettlement.StartTime)
                .Value(BusinessSettlement._EndTime, businessSettlement.EndTime)
                .Value(BusinessSettlement._SPCode, businessSettlement.SPCode)
                .Value(BusinessSettlement._ChannelID, businessSettlement.ChannelID)
                .Value(BusinessSettlement._Type, businessSettlement.Type)
                .Value(BusinessSettlement._InfoMoney, businessSettlement.InfoMoney)
                .Value(BusinessSettlement._TotalPrice, businessSettlement.TotalPrice)
                .Value(BusinessSettlement._BadMoney, businessSettlement.BadMoney)
                .Value(BusinessSettlement._GetMoney, businessSettlement.GetMoney)
                .Value(BusinessSettlement._Status, businessSettlement.Status)
                .ToString();
            bool istrue = dbkey.IsNullEmpty() ? Data.ExecSql(strSql) == 1 : Data.Pool(dbkey).ExecSql(strSql) == 1;
            if (delCache.IsNull()) return istrue;
            foreach (string s in delCache) {
                if (s.IndexOf("(") == -1 || s.IndexOf("[") == -1)
                    Cache2.Remove(s.IndexOf("Cache_") == -1 ? "GTS.MiniBox.BusinessSettlementCache_" + s : s);
                else
                    Cache2.RemoveByPattern(s.IndexOf("Cache_") == -1 ? "(GTS.MiniBox.BusinessSettlementCache_" + s + ")" : s);
            }
            return istrue;
        }
        /// <summary>
        /// 添加记录
        /// </summary>
        /// <param name="businessSettlement">实体类</param>
        /// <returns>添加是否成功</returns>
        public static bool Insert(BusinessSettlement businessSettlement, string dbkey = "") {
            return Insert(businessSettlement, dbkey, null);
        }
        /// <summary>
        /// 修改记录
        /// </summary>
        /// <param name="businessSettlement">实体类</param>
        /// <param name="where">修改时附加条件，统一的前面要加链接符（and、or等等）</param>
        /// <param name="delCache">修改成功后清理的CACHE key，支持正则</param>
        /// <param name="dbkey">存在数据库连接池中的连接key，为空时使用ConnString连接</param>
        /// <returns>修改是否成功</returns>
        public static bool Update(BusinessSettlement businessSettlement, string dbkey = "", string where = "", string[] delCache = null) {
            if (businessSettlement.StartTime.IsNullEmpty()  && businessSettlement.EndTime.IsNullEmpty()  && businessSettlement.SPCode.IsNullEmpty()  && businessSettlement.ChannelID.IsNull()  && businessSettlement.Type.IsNull()) return false;
            string strSql = new SQL().Update(BusinessSettlement._)
                .Set(BusinessSettlement._InfoMoney, businessSettlement.InfoMoney)
                .Set(BusinessSettlement._TotalPrice, businessSettlement.TotalPrice)
                .Set(BusinessSettlement._BadMoney, businessSettlement.BadMoney)
                .Set(BusinessSettlement._GetMoney, businessSettlement.GetMoney)
                .Set(BusinessSettlement._Status, businessSettlement.Status)
                .Where("1=1").Where(new Where()
                    .And(BusinessSettlement._StartTime, businessSettlement.StartTime, Operator.Equal, true)
                    .And(BusinessSettlement._EndTime, businessSettlement.EndTime, Operator.Equal, true)
                    .And(BusinessSettlement._SPCode, businessSettlement.SPCode, Operator.Equal, true)
                    .And(BusinessSettlement._ChannelID, businessSettlement.ChannelID, Operator.Equal, true)
                    .And(BusinessSettlement._Type, businessSettlement.Type, Operator.Equal, true)
                ).ToString();
            if (!where.IsNullEmpty()) strSql += where;
            int value = dbkey.IsNullEmpty() ? Data.ExecSql(strSql) : Data.Pool(dbkey).ExecSql(strSql);
            if (value <= 0) return false;
            if (delCache.IsNull()) return true;
            foreach (string s in delCache) {
                if (s.IndexOf("(") == -1 || s.IndexOf("[") == -1)
                    Cache2.Remove(s.IndexOf("Cache_") == -1 ? "GTS.MiniBox.BusinessSettlementCache_" + s : s);
                else
                    Cache2.RemoveByPattern(s.IndexOf("Cache_") == -1 ? "(GTS.MiniBox.BusinessSettlementCache_" + s + ")" : s);
            }
            return true;
        }
        /// <summary>
        /// 修改记录
        /// </summary>
        /// <param name="businessSettlement">实体类</param>
        /// <returns>修改是否成功</returns>
        public static bool Update(BusinessSettlement businessSettlement, string dbkey = "") {
            return Update(businessSettlement, dbkey, "", null);
        }
          /// <summary>
        /// 删除记录
        /// </summary>
        /// <param name="startTime">StartTime</param>
        /// <param name="endTime">EndTime</param>
        /// <param name="sPCode">SPCode</param>
        /// <param name="channelID">通道ID</param>
        /// <param name="type">通道类型</param>
        /// <param name="where">修改时附加条件，统一的前面要加链接符（and、or等等）</param>
        /// <param name="delCache">删除成功后清理的CACHE key，支持正则</param>
        /// <param name="dbkey">存在数据库连接池中的连接key，为空时使用ConnString连接</param>
        /// <returns>删除是否成功</returns>
        public static bool DeleteByID(string startTime, string endTime, string sPCode, int? channelID, int? type,  string dbkey = "", string where = "", string[] delCache = null) {
            if (startTime.IsNullEmpty()  && endTime.IsNullEmpty()  && sPCode.IsNullEmpty()  && channelID.IsNull()  && type.IsNull()) return false;
            string strSql = new SQL().Delete(BusinessSettlement._)
                .Where("1=1").Where(new Where()
                    .And(BusinessSettlement._StartTime, startTime, Operator.Equal, true)
                    .And(BusinessSettlement._EndTime, endTime, Operator.Equal, true)
                    .And(BusinessSettlement._SPCode, sPCode, Operator.Equal, true)
                    .And(BusinessSettlement._ChannelID, channelID, Operator.Equal, true)
                    .And(BusinessSettlement._Type, type, Operator.Equal, true)
                ).ToString();
            if (!where.IsNullEmpty()) strSql += where;
            int value = dbkey.IsNullEmpty() ? Data.ExecSql(strSql) :  Data.Pool(dbkey).ExecSql(strSql);
            if (value != 1) return false;
            if (delCache.IsNull()) return true;
            foreach (string s in delCache) {
                if (s.IndexOf("(") == -1 || s.IndexOf("[") == -1)
                    Cache2.Remove(s.IndexOf("Cache_") == -1 ? "GTS.MiniBox.BusinessSettlementCache_" + s : s);
                else
                    Cache2.RemoveByPattern(s.IndexOf("Cache_") == -1 ? "(GTS.MiniBox.BusinessSettlementCache_" + s + ")" : s);
            }
            return true;
        }
        /// <summary>
        /// 删除记录
        /// </summary>
        /// <param name="startTime">StartTime</param>
        /// <param name="endTime">EndTime</param>
        /// <param name="sPCode">SPCode</param>
        /// <param name="channelID">通道ID</param>
        /// <param name="type">通道类型</param>
        /// <returns>删除是否成功</returns>
        public static bool DeleteByID(string startTime, string endTime, string sPCode, int? channelID, int? type, string dbkey = "") {
            return DeleteByID(startTime, endTime, sPCode, channelID, type,  dbkey, "", null);
        }
        /// <summary>
        /// 删除记录
        /// </summary>
        /// <param name="startTime">StartTime</param>
        /// <param name="endTime">EndTime</param>
        /// <param name="sPCode">SPCode</param>
        /// <param name="channelID">通道ID</param>
        /// <param name="type">通道类型</param>
        /// <param name="where">修改时附加条件，统一的前面要加链接符（and、or等等）</param>
        /// <param name="delCache">删除成功后清理的CACHE key，支持正则</param>
        /// <param name="dbkey">存在数据库连接池中的连接key，为空时使用ConnString连接</param>
        /// <returns>删除是否成功</returns>
        public static bool DeleteByID(string startTime, string endTime, string sPCode, int channelID, int type,  string dbkey = "", string where = "", string[] delCache = null) {
            return DeleteByID((string)startTime, (string)endTime, (string)sPCode, (int?)channelID, (int?)type,  dbkey, where, delCache);
        }
        /// <summary>
        /// 删除记录
        /// </summary>
        /// <param name="startTime">StartTime</param>
        /// <param name="endTime">EndTime</param>
        /// <param name="sPCode">SPCode</param>
        /// <param name="channelID">通道ID</param>
        /// <param name="type">通道类型</param>
        /// <returns>删除是否成功</returns>
        public static bool DeleteByID(string startTime, string endTime, string sPCode, int channelID, int type, string dbkey = "") {
            return DeleteByID((string)startTime, (string)endTime, (string)sPCode, (int?)channelID, (int?)type,  dbkey, "", null);
        }
        /// <summary>
        /// 按主键查询，返回数据的实体类
        /// </summary>
        /// <param name="startTime">StartTime</param>
        /// <param name="endTime">EndTime</param>
        /// <param name="sPCode">SPCode</param>
        /// <param name="channelID">通道ID</param>
        /// <param name="type">通道类型</param>
        /// <param name="where">附加条件，统一的前面要加链接符（and、or等等）</param>
        /// <param name="dbkey">存在数据库连接池中的连接key，为空时随机取连接key</param>
        /// <returns>返回单条记录的实体类</returns>
        public static BusinessSettlement SelectByID(string startTime, string endTime, string sPCode, int channelID, int type,  string dbkey = "", string where = "") {
            string cacheNameKey = "GTS.MiniBox.BusinessSettlementCache_SelectByID_{0}".FormatWith(startTime + "_" + endTime + "_" + sPCode + "_" + channelID + "_" + type + "_" +  "_" + where);
            return Cache2.Get<BusinessSettlement>(cacheNameKey, cacheSeconds, () => {
                string strSql = new SQL().From(BusinessSettlement._)
                    .Select(BusinessSettlement._StartTime)
                    .Select(BusinessSettlement._EndTime)
                    .Select(BusinessSettlement._SPCode)
                    .Select(BusinessSettlement._ChannelID)
                    .Select(BusinessSettlement._Type)
                    .Select(BusinessSettlement._InfoMoney)
                    .Select(BusinessSettlement._TotalPrice)
                    .Select(BusinessSettlement._BadMoney)
                    .Select(BusinessSettlement._GetMoney)
                    .Select(BusinessSettlement._Status)
                    .Where("1=1").Where(new Where()
                        .And(BusinessSettlement._StartTime, startTime, Operator.Equal)
                        .And(BusinessSettlement._EndTime, endTime, Operator.Equal)
                        .And(BusinessSettlement._SPCode, sPCode, Operator.Equal)
                        .And(BusinessSettlement._ChannelID, channelID, Operator.Equal)
                        .And(BusinessSettlement._Type, type, Operator.Equal)
                    ).ToString();
                if (!where.IsNullEmpty()) strSql += where;
                return Data.Pool(dbkey).GetDbDataReader(strSql).ToList<BusinessSettlement>().FirstOrDefault();
            });
        }
        /// <summary>
        /// 按主键查询，返回数据的实体类
        /// </summary>
        /// <param name="startTime">StartTime</param>
        /// <param name="endTime">EndTime</param>
        /// <param name="sPCode">SPCode</param>
        /// <param name="channelID">通道ID</param>
        /// <param name="type">通道类型</param>
        /// <returns>返回单条记录的实体类</returns>
        public static BusinessSettlement SelectByID(string startTime, string endTime, string sPCode, int channelID, int type, string dbkey = "") {
            return SelectByID(startTime, endTime, sPCode, channelID, type,  dbkey, "");
        }
        /// <summary>
        /// 清除按主键查询的缓存
        /// </summary>
        public static void ClearCacheSelectByID(string startTime, string endTime, string sPCode, int channelID, int type,  string where = "") {
            string cacheName = "GTS.MiniBox.BusinessSettlementCache_SelectByID_{0}";
            string cacheNameKey = string.Format(cacheName, startTime + "_" + endTime + "_" + sPCode + "_" + channelID + "_" + type + "_" +  "_" + where);
            Cache2.Remove(cacheNameKey);
        }
        /// <summary>
        /// 查询数据，带分页
        /// </summary>
        /// <param name="pageIndex">当前第几页，从1开始</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="totalRecords">返回总记录数</param>
        /// <param name="where">附加条件，统一的前面要加链接符（and、or等等）</param>
        /// <param name="order">排序字段，不加“order by”</param>
        /// <param name="fieldList">设置需要返回的字段</param>
        /// <param name="dbkey">存在数据库连接池中的连接key，为空时随机取连接key</param>
        /// <param name="pageEnum">使用哪种分页方式（not_in、max_top、top_top、row_number、mysql、oracle、sqlite）</param>
        /// <returns>返回实体记录集</returns>
        public static IList<BusinessSettlement> SelectPageList(int pageIndex, int pageSize, out int totalRecords, string dbkey = "", string where = "", string order = "", string fieldList = "", PagerSQLEnum pageEnum = PagerSQLEnum.row_number) {
            totalRecords = 0;
            string cacheNameKey = "GTS.MiniBox.BusinessSettlementCache_SelectPageList_{0}_{1}_{2}_{3}_{4}".FormatWith(pageIndex, pageSize, where, order, fieldList);
            string cacheRecordsKey = "GTS.MiniBox.BusinessSettlementCache_RecordsSelectPageList_{0}_{1}_{2}_{3}_{4}".FormatWith(pageIndex, pageSize, where, order, fieldList);
            IList<BusinessSettlement> list = (IList<BusinessSettlement>)Cache2.Get(cacheNameKey);
            if (!list.IsNull()) { totalRecords = (int)Cache2.Get(cacheRecordsKey); return list; }

            using (PagerSQLHelper s = new PagerSQLHelper(pageEnum)) {
                PagerSql sql = s.GetSQL(pageIndex, pageSize, BusinessSettlement._, BusinessSettlement._StartTime, fieldList.IfNullOrEmpty("[StartTime],[EndTime],[SPCode],[ChannelID],[Type],[InfoMoney],[TotalPrice],[BadMoney],[GetMoney],[Status],"), where, "", order);
                DataSet ds = Data.Pool(dbkey).GetDataSet(sql.DataSql + ";" + sql.CountSql);
                if (ds.IsNull()) return list;
                list = ds.Tables[0].ToList<BusinessSettlement>();
                totalRecords = ds.Tables[1].Rows[0][0].ToInt();
                ds.Dispose(); ds = null;
            }
            Cache2.Insert(cacheNameKey, list, cacheSeconds);
            Cache2.Insert(cacheRecordsKey, totalRecords, cacheSeconds);
            return list;
        }
        /// <summary>
        /// 查询记录，带分页
        /// </summary>
        /// <param name="pageIndex">当前第几页，从1开始</param>
        /// <param name="pageSize">每页记录数</param>
        /// <param name="totalRecords">返回总记录数</param>
        /// <returns>返回实体记录集</returns>
        public static IList<BusinessSettlement> SelectPageList(int pageIndex, int pageSize, out int totalRecords, string dbkey = "") {
            return SelectPageList(pageIndex, pageSize, out totalRecords, dbkey, "", "", "", PagerSQLEnum.row_number);
        }
        /// <summary>
        /// 清除查询记录，带分页的缓存
        /// </summary>
        public static void ClearCacheSelectPageList() {
            string cacheNameKey = "GTS.MiniBox.BusinessSettlementCache_SelectPageList_(.+?)";
            string cacheRecordsKey = "GTS.MiniBox.BusinessSettlementCache_RecordsSelectPageList_(.+?)";
            Cache2.RemoveByPattern(cacheNameKey);
            Cache2.RemoveByPattern(cacheRecordsKey);
        }
    }
}

